using System;
using System.Data;
using System.Data.OleDb;

using Geekees.App.Cashbook.Lib.Entity;
using Geekees.App.Cashbook.Lib.Helper;

namespace Geekees.App.Cashbook.Lib.DAL
{
	/// <summary>
	/// Summary description for DACategory.
	/// </summary>
	public class DACategory
	{
		public DACategory()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		public static DataTable GetCategoryByClassificationId( int userId, int classificationId )
		{
			string sql = "SELECT * FROM [Category] WHERE UserId=@UserId AND ClassificationId=@ClassificationId";
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			cmd.Parameters.Add( "@ClassificationId", OleDbType.Integer ).Value = classificationId;
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				DataTable dt = h.ExecuteDataTable( cmd );

				return dt;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }

		}

		public static DataTable GetAllCategory( int userId )
		{
			string sql = "SELECT * FROM [Category] WHERE UserId=@UserId";
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				DataTable dt = h.ExecuteDataTable( cmd );

				return dt;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
		}

		public static int AddCategory( string cName, int userId, int classificationId )
		{
			int i = Consts.NO_ID;
			string sql = "INSERT INTO [Category] ( Name, SortIndex, UserId, ClassificationId ) VALUES " + 
				"( @Name,  @SortIndex, @UserId, @ClassificationId ) ";
			string sqlId= " SELECT MAX(Id) AS ThisID FROM [Category]";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Name", OleDbType.VarChar ).Value = cName;
			cmd.Parameters.Add( "@SortIndex", OleDbType.Integer ).Value = 0;
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			cmd.Parameters.Add( "@ClassificationId", OleDbType.Integer ).Value = classificationId;
			
			OleDbCommand cmdId = new OleDbCommand( sqlId );
			
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				h.ExecuteScalar( cmd );
				i = (int)h.ExecuteScalar( cmdId );
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
			
			return i;
		}

		public static bool UpdateCategory( int cId, string cName, int userId, int classificationId )
		{
			bool result = false;
			string sql = "UPDATE [Category] SET [Name]=@Name,[ClassificationId]=@ClassificationId WHERE [Id]=@Id AND [UserId]=@UserId";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Name", OleDbType.VarChar ).Value = cName;
			cmd.Parameters.Add( "@ClassificationId", OleDbType.Integer ).Value = classificationId;
			cmd.Parameters.Add( "@Id", OleDbType.Integer ).Value = cId;
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				result = h.ExecuteNonQuery( cmd ) > 0;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
			
			return result;
		}

		public static bool DeleteCategory( int cId, int userId )
		{
			bool result = false;
			string sql = "DELETE FROM [Category] WHERE [Id]=@Id AND [UserId]=@UserId";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Id", OleDbType.Integer ).Value = cId;
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				result = h.ExecuteNonQuery( cmd ) > 0;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
			
			return result;
		}

		public static bool DeleteCategoryByClassification( int classificationId, int userId )
		{
			bool result = false;
			string sql = "DELETE FROM [Category] WHERE [ClassificationId]=@ClassificationId AND [UserId]=@UserId";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@ClassificationId", OleDbType.Integer ).Value = classificationId;
			cmd.Parameters.Add( "@UserId", OleDbType.Integer ).Value = userId;
			
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				result = h.ExecuteNonQuery( cmd ) > 0;
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
			
			return result;
		}
	}
}
